package guoanmaker.operator.business.repository;

import java.util.Date;
import java.util.List;

import javax.persistence.NamedStoredProcedureQuery;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import guoanmaker.operator.business.model.ProvideOrderForm;


public interface ProvideOrderFormRepository extends  JpaRepository<ProvideOrderForm, String>, JpaSpecificationExecutor<ProvideOrderForm>{

	@Query("select o from ProvideOrderForm o left  join o.commodity p   where  o.commodity.id=:id")
	public  List<ProvideOrderForm>  selectBycommodity(@Param("id") String id);
	
	@Query("select o from ProvideOrderForm o left  join o.commodity p   where  o.couponid=:couponid")
	public  List<ProvideOrderForm>  selectByCouponid(@Param("couponid") String couponid);
	
	@Query("select COALESCE(SUM(o.realPrice),0)   from ProvideOrderForm  o left join o.user u where u.id=:id and (o.orderStatus='6' or o.orderStatus='13') ")
	public  Double    selectuserSumrRate1(@Param("id") String id);
	
	@Query("select COALESCE(SUM(o.realPrice),0) from ProvideOrderForm o left  join o.commodity c  left join c.providerBusiness b left join c.serviceType t  where  b.id=:id and (o.orderStatus='6' or o.orderStatus='13') ")
	public  Double  selectSumrRate(@Param("id") String id);
	
	@Query("select count(*) from ProvideOrderForm  o left  join  o.user u where o.user.id=:id")
	public  int  selectBycountuTotalOrder(@Param("id") String id);
	@Query("select count(*) from ProvideOrderForm  o left  join o.commodity c  left join c.providerBusiness b left join c.serviceType t where  b.id=:id")
	public  int  selectBycountTotalOrder(@Param("id") String id);
	
	@Query("select count(*) from ProvideOrderForm  o left  join o.user u where u.id=:id")
	public  int  selectByordernumber(@Param("id") String id);
	
	@Query("select count(*) from ProvideOrderForm  o left  join o.user u where o.user.id=:id and (o.orderStatus='6' or o.orderStatus='13') ")
	public  int  selectsucordernumber(@Param("id") String id);
	
	@Query("select count(*) from  ProvideOrderForm   o left  join o.commodity c left join c.providerBusiness b left join c.serviceType t  where  b.id=:id and (o.orderStatus='6' or o.orderStatus='13') ")
	public  int  selectBycountvolume(@Param("id") String id);
	
	@Query("select count(*) from  ProvideOrderForm   o left  join o.commodity c left join c.providerBusiness b left join c.serviceType t  where  b.id=:id and (o.orderStatus='5' or o.orderStatus='9' or o.orderStatus='10')")
	public  int  selectSinglebdisputenum(@Param("id") String id);
	
	@Query("select o from ProvideOrderForm o left  join o.user u where u.id=:id")
	public  List<ProvideOrderForm>  selectByPersonUser(@Param("id") String id);
	
	
	/*@Query("select sum(o.realPrice) from ProvideOrderForm o left  join o.user u where o.user.id=:id and o.orderStatus='6'")
	public  double  selectByconsumption(@Param("id") String id);
	*/
	@Query("select count(*) from ProvideOrderForm  o left  join o.user u where o.user.id=:id and (o.orderStatus='5' or o.orderStatus='9' or o.orderStatus='10')")
	public  int  selectSingledisputenum(@Param("id") String id);
	

	@Query("select count(o.id) from ProvideOrderForm o left join o.user u left join o.commodity c left join c.providerBusiness b left join c.serviceType t where u.id=:id  and t.serverName =:serverName")
	public int  selectBycountOid(@Param("id") String id,@Param("serverName") String serverName);
	
	/**
	 * 秒杀人数
	 * @param id
	 * @param starttime
	 * @param endtime
	 * @return
	 */
	
	@Query("select count(o.id) from ProvideOrderForm  o left join o.user u left join o.commodity c left join c.providerBusiness b left join c.serviceType t  where o.spikeid is not null and o.spikeid !='' and o.commodity.id=:id and o.placeOrderTime>=:starttime and o.placeOrderTime<=:endtime")
	public  int  selectSpikenum(@Param("id") String id,@Param("starttime") Date starttime,@Param("endtime") Date endtime);
	
	
}
